import os,datetime

from utils.DBService import sqlquery

BANHEZHAN_SQL = """
SELECT q.testname,subquery.departname
, SUM(a.zongshu) zongshu
, SUM(a.hege) hege
, CONVERT(DECIMAL(10, 2), SUM(CONVERT(FLOAT,a.hege))/SUM(CONVERT(FLOAT, a.zongshu)) * 100) as hegelv
FROM T_SYJZBView as q 
JOIN (
    SELECT b.gprsbianhao , c.departname
    FROM t_s_depart AS c
    JOIN Banhezhanxinxi AS b ON c.id = b.departid
    WHERE b.orgcode LIKE (SELECT org_code FROM t_s_depart WHERE id = '{}') + '%'
    AND b.gprstype NOT IN (1, 2, 5)
) AS subquery ON q.SBBH = subquery.gprsbianhao
JOIN (
SELECT a.zongshu
, a.hege
, a.shebeibianhao
FROM TONGJISYS a
WHERE {}
) as a on a.shebeibianhao = subquery.gprsbianhao
{}
{}
GROUP BY q.testname,subquery.departname
"""

def shiyan_ovStatic(Args,user):
    print('Quest参数:\n\t',Args)
    depId = Args['depId']
    dates = Args['dates']
    depId = '\',\''.join(depId.split(','))

    if depId == '8a8ab0b246dc81120146dc8180ba0017'or \
            depId == 'f632d53969b410ac0169bd10699f0044'or \
            depId == 'All':
        depId = 'f632d53969b410ac0169bd10699f0044'
    elif depId == 'f6323b406b4fab1b016bacabdb9f01d3':
        depId = 'f6323b406b4fab1b016bacabdb9f01d3'
    elif depId == 'f6323b406b4fab1b016bacac674901d5':
        depId = 'f6323b406b4fab1b016bacac674901d5'
    elif depId == 'f6323b406d19fc87016d1a5c43910016':
        depId = 'f6323b406d19fc87016d1a5c43910016'
    elif depId == 'f6323b406d19fc87016d1a5b861b0014':
        depId = 'f6323b406d19fc87016d1a5b861b0014'
    else:
        depId = 'f632d53969b410ac0169bd10699f0044'

    dateWhere = 'CONVERT(nvarchar(10), a.SYRQ, 120)in (\'{}\')'
    dateWhereq = 'and CONVERT(nvarchar(10), q.SYRQ, 120)in (\'{}\')'
    datearray = dates.split(',')
    if len(datearray) == 2:
        dateWhere = 'CONVERT(nvarchar(10), a.SYRQ, 120)>=\'{}\' and CONVERT(nvarchar(10), a.SYRQ, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
        dateWhereq = 'and CONVERT(nvarchar(10), q.SYRQ, 120)>=\'{}\' and CONVERT(nvarchar(10), q.SYRQ, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))
        dateWhereq = dateWhereq.format('\',\''.join(dates.split(',')))

    jietou = "and q.testname = '钢筋焊接接头试验'"
    jixiejietou = "and q.testname = '钢筋机械连接接头试验'"
    gangjin = "and q.testname = '钢筋试验'"
    hntkangya = "and q.testname = '混凝土试件抗压强度试验'"
    banhezhanjietouSql = BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,jietou)
    print('SQL:\n\t', banhezhanjietouSql)
    banhezhanData1 = sqlquery(BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,jietou))

    banhezhanjixiejietouSql = BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,jixiejietou)
    print('SQL:\n\t', banhezhanjixiejietouSql)
    banhezhanData2 = sqlquery(BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,jixiejietou))

    banhezhangangjinSql = BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,gangjin)
    print('SQL:\n\t', banhezhangangjinSql)
    banhezhanData3 = sqlquery(BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,gangjin))

    banhezhanhntkangyaSql = BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,hntkangya)
    print('SQL:\n\t', banhezhanhntkangyaSql)
    banhezhanData4 = sqlquery(BANHEZHAN_SQL.format(depId,dateWhere,dateWhereq,hntkangya))

    slData = []
    slData += [process_data(data) for data in banhezhanData1]
    slData += [process_data(data) for data in banhezhanData2]
    slData += [process_data(data) for data in banhezhanData3]
    slData += [process_data(data) for data in banhezhanData4]
    print('Data:\n\t', slData)

    resdata = []
    if slData.__len__() == 0:
        marktitle = '当前时间没有数据'
        return resdata,slData,marktitle
    else:
        if len(datearray) == 2:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            end = datetime.datetime.strptime(datearray[1], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '到' + str(
                end.year) + '年' + str(end.month) + '月' + str(end.day) + '日' + '试验任务情况如下'
        else:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            marktitle = str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '试验任务情况如下:'

    children = []
    a_list = []
    b_list = []
    c_list = []
    d_list = []
    for index, item in enumerate(slData):
        station_name = item[0]
        a = item[0:]
        data = {
            'key': index,
            'departname': a[0],
            'cjpanshu': a[1],
            'cjichaobL': a[2],
            'zjpanshu': float(a[3])
        }
        table_head = [
            {
                "title": '试验类型',
                "dataIndex": list(data.keys())[2],
                "key": list(data.keys())[2],
            },
            {
                "title": '组数',
                "dataIndex": list(data.keys())[3],
                "key": list(data.keys())[3],
            },
            {
                "title": '合格率(%)',
                "dataIndex": list(data.keys())[4],
                "key": list(data.keys())[4],
            }]
        if station_name.startswith("武黄1标"):
            a_list.append(data)
        elif station_name.startswith("武黄2标") :
            b_list.append(data)
        elif station_name.startswith("武黄3标") :
            c_list.append(data)
        else:
            d_list.append(data)
    if len(a_list)>0:
        add_totals(a_list)
        childrenA_Data = {"children":[{"title": '武黄1标试验室', "data": {"tableHead": table_head, "tableBody": a_list , 'echarts' : pro_data(a_list)}}] ,"title": '武黄1标'}
    children.append(childrenA_Data)
    print(a_list,"------------------------------------")
    if len(b_list)>0:
        add_totals(b_list)
        childrenB_Data = {"children":[{"title": '武黄2标试验室', "data": {"tableHead": table_head, "tableBody": b_list , 'echarts' : pro_data(b_list)}}] ,"title": '武黄2标'}
        children.append(childrenB_Data)
    print(b_list,"------------------------------------")
    if len(c_list)>0:
        add_totals(c_list)
        childrenB_Data = {"children":[{"title": '武黄3标试验室', "data": {"tableHead": table_head, "tableBody": c_list , 'echarts' : pro_data(c_list)}}] ,"title": '武黄3标'}
        children.append(childrenB_Data)
    if len(d_list)>0:
        add_totals(d_list)
        childrenB_Data = {"children":[{"title": '武黄高速监理', "data": {"tableHead": table_head, "tableBody": d_list , 'echarts' : pro_data(d_list)}}] ,"title": '武黄高速监理'}
        children.append(childrenB_Data)
    result = [{"title": "武黄高速", "children": children}]
    return slData,result,marktitle

def pro_data(data):
    xdata = []
    ydata = []
    zdata = []
    for item in data:
        if item['cjpanshu'].startswith("混凝土试件抗压"):
            xdata.append("混凝土抗压")
            ydata.append({
                'value': item['zjpanshu'],
                'itemStyle': {
                    'color': '#FFBE8D'
                }
            })
            zdata.append({
                'value': item['cjichaobL'],
                'itemStyle': {
                    'color': '#6394F9'
                }
            })
            continue
        if item['cjpanshu'].startswith("钢筋试验"):
            xdata.append("钢筋原材")
            ydata.append({
                'value': item['zjpanshu'],
                'itemStyle': {
                    'color': '#FFBE8D'
                }
            })
            zdata.append({
                'value': item['cjichaobL'],
                'itemStyle': {
                    'color': '#69DBAE'
                }
            })
            continue
        if item['cjpanshu'].startswith("钢筋焊接"):
            xdata.append("钢筋焊接")
            ydata.append({
                'value': item['zjpanshu'],
                'itemStyle': {
                    'color': '#FFBE8D'
                }
            })
            zdata.append({
                'value': item['cjichaobL'],
                'itemStyle': {
                    'color': '#EECD67'
                }
            })
            continue
        if item['cjpanshu'].startswith("钢筋机械连接"):
            xdata.append("钢筋机械连接")
            ydata.append({
                'value': item['zjpanshu'],
                'itemStyle': {
                    'color': '#FFBE8D'
                }
            })
            zdata.append({
                    'value': item['cjichaobL'],
                    'itemStyle': {
                        'color': '#E49865'
                    }
                })
            continue
    option = {
                'tooltip': {
                    'trigger': 'axis',
                    'axisPointer': {
                        'type': 'cross'
                    }
                },
                'grid': {
                    'right': '20%'
                },
                'toolbox': {
                    'feature': {
                        'dataView': { 'show': 'true', 'readOnly': 'false' },
                        'restore': { 'show': 'true' },
                        'saveAsImage': { 'show': 'true' }
                    }
                },

                'xAxis': [
                    {
                        'type': 'category',
                        'axisTick': {
                            'alignWithLabel': 'true'
                        },
                        'data': xdata
                    }
                ],
                'yAxis': [
                    {
                        'type': 'value',
                        'name': '合格率(%)',
                        'position': 'right',
                        'alignTicks': 'true',
                        'axisLine': {
                            'show': 'true',
                            'lineStyle': {
                                'color': '#8C8C8C'
                            }
                        },
                        'axisLabel': {
                            'formatter': '{value} %'
                        }
                    },
                    {
                        'type': 'value',
                        'name': '试验组数(组)',
                        'position': 'left',
                        'alignTicks': 'true',
                        'axisLine': {
                            'show': 'true',
                            'lineStyle': {
                                'color': '#8C8C8C'
                            }
                        },
                        'axisLabel': {
                            'formatter': '{value}'
                        }
                    }
                ],
                'series': [
                    {
                        'name': '合格率(%)',
                        'type': 'line',
                        'color': '#E49865',
                        'data': ydata
                    },
                    {
                        'name': '试验组数(组)',
                        'type': 'bar',
                        'yAxisIndex': 1,
                        'data': zdata,
                        "barWidth": "50"
                    }
                ]
            };
    return option

def add_totals(data):
    # 计算合计值
    total_cjichaobL = sum(item["cjichaobL"] for item in data)
    total_zjpanshu = sum(item["zjpanshu"] for item in data)

    # 添加合计项
    total_item = {
        "cjichaobL": total_cjichaobL,
        "cjpanshu": "合计",
        "departname": "",
        "key": "",
        "zjpanshu": round(total_zjpanshu/data.__len__(),2)
    }

    data.append(total_item)

    return data

def process_data(data):
    if os.environ.get("ENV_NAME") == '1':
        return [data[1], data[0], data[2] if data[2] is not None else 0.0,
                data[4] if data[4] is not None else 0.0]
    else:
        return [data[1], data[0].encode('latin1').decode('gbk'),
                data[2] if data[2] is not None else 0.0,
                data[4] if data[4] is not None else 0.0]

functionConf = {
    "name": "shiyan_ovStatic",
    "description": "key point:试验任务情况。Check the trial status of each project site by ID and time",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs. 武黄1标是(f6323b406b4fab1b016bacabdb9f01d3).武黄2标是(f6323b406b4fab1b016bacac674901d5).问武黄传参数:武黄高速"
            },
            "dates": {
                "type": "string",
                "description": "Date(s) of material production in yyyy-MM-dd format. Use comma-separated values (e.g., '2023-07-14,2023-07-15')."
            }
        },
        "required": [
            "depId",
            "dates"
        ]
    }
}